/*Test Case Created by Steven Chow
 * This Test Cases tests for all the methods 
 * within the Reservation Database
 * 
 * All Tests are independant from each other.
 */
package com.JUnitTestsDB;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

import com.Helper.AirplaneStatus;
import com.database.CustomerDB;
import com.database.FlightDB;
import com.database.ReservationDB;

public class ReservationDBTest {
	
	//Initialize Fields
	private Connection connection;
	private int reservationID;
	private String firstName;
	private String lastName;
	private float paidPrice;
	private int paidPoint;
	private String meal;
	private String seat;
	private String username;
	private String flightID;	
	
	//Setup connect to DB
	public ReservationDBTest()
	{
		String connectionString = "jdbc:mysql://localhost/planesimple?"   //CHANGE THIS PART!!!
              + "user=local&password=bluebeariscute89";
		try {
			// Ensure the SQL Server driver class is available.
			Class.forName("com.mysql.jdbc.Driver");

			// Establish the connection.
			connection = DriverManager.getConnection(connectionString);
		}
		// Exception handling
		catch (ClassNotFoundException cnfe) {

			System.out.println("ClassNotFoundException " + cnfe.getMessage());
		} catch (Exception e) {
			System.out.println("Exception " + e.getMessage());
			e.printStackTrace();
		}	
	}

	// Need this to test whether or not the flight is in the database
	private boolean ReservationExists(String id) throws SQLException {
		Statement stmt = connection.createStatement();
		String sqlString = "select * from " + "Reservation" + " WHERE username = " +
				"'" + id + "'";
		ResultSet rs = stmt.executeQuery(sqlString);

		if (rs.next()) {			
			this.reservationID = Integer.parseInt(rs.getString("reservationID"));
			this.firstName = rs.getString("firstName");
			this.lastName = rs.getString("lastName");
			this.paidPrice = Integer.parseInt(rs.getString("paidPrice"));
			this.paidPoint = Integer.parseInt(rs.getString("paidPoint"));
			this.meal = rs.getString("meal");
			this.seat = rs.getString("seat");
			this.username = rs.getString("username");
			this.flightID = rs.getString("flightID");
			return true;
		} else {
			return false;
		}
	}
	
	//For testing Modify Attributes
	private String GetAttributeFromDB(int columnNum, int tupleID) throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sqlString = "select * from " + "Reservation" + " where reservationID = " +  "'" + tupleID + "'";		
		ResultSet rs = stmt.executeQuery(sqlString);		
		if (rs.next())
		{
			this.reservationID = Integer.parseInt(rs.getString("reservationID"));
			switch(columnNum)
			{
			case 1:
				this.firstName = rs.getString("firstName");
				return this.firstName;
			case 2:
				this.lastName = rs.getString("lastName");
				return this.lastName;
			case 3:
				this.paidPrice = Integer.parseInt(rs.getString("paidPrice"));
				return rs.getString("paidPrice");
			case 4:
				this.paidPoint  = Integer.parseInt(rs.getString("paidPoint"));
				return rs.getString("paidPoint");
			case 5:
				this.meal = rs.getString("meal");
				return this.meal;
			case 6:
				this.seat = rs.getString("seat");
				return this.seat;
			case 7:
				this.username = rs.getString("username");
				return this.username;
			case 8:
				this.flightID = rs.getString("FlightID");
				return flightID;
			}
		}	
		//tuple or Column Number Does not exist
		return null;	
	}
	
	//Test 1 if we can add a reservation
	@Test
	public void testInsert() {
		//Add a Customer
		CustomerDB dbCus = new CustomerDB ("ehan", "pass", "edward", "han", "ehan@mail.com", 123, 0);
		dbCus.Setup(connection);
		//Add a Flight
		FlightDB dbFlight =  new FlightDB("AC8080", "Air Canada", "Vancouver", "2012-11-15 10:00:00", 
				   "Edmonton", "2012-11-15 12:00:00", AirplaneStatus.onTime, 10f,
				   10, "1A", 200f
				  );
		dbFlight.Setup(connection);
		//Add a Reservation
		ReservationDB db = new ReservationDB("edward", "han", 150f, 0, "chicken","asdf", "ehan", "AC8080");
		db.Setup(connection);
		try {
			dbCus.Insert();
			dbFlight.Insert();
			db.Insert();
			assertEquals(true, ReservationExists("ehan"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/*
	//Test 2 if we can delete a reservation
	@Test
	public void testDelete() {
		//Add a Customer
		CustomerDB dbCus = new CustomerDB ("ehan1", "pass", "edward", "han", "ehan@mail.com", 123, 0);
		dbCus.Setup(connection);
		//Add a Flight
		FlightDB dbFlight =  new FlightDB("AC8081", "Air Canada", "Vancouver", "2012-11-15 10:00:00", 
				   "Edmonton", "2012-11-15 12:00:00", AirplaneStatus.onTime, 10f,
				   10, "1A", 200f
				  );
		dbFlight.Setup(connection);
		//Add a Reservation
		ReservationDB db = new ReservationDB("edward", "han", 150f, 0, "chicken","asdf", "ehan1", "AC8081");
		db.Setup(connection);
		try {
			dbCus.Insert();
			dbFlight.Insert();
			db.Insert();
			db.Delete("2");
			assertEquals(false, ReservationExists("ehan1"));
			dbFlight.Delete("AC8081");
			dbCus.Delete("ehan1");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
*/
	//Test 3 a reservation does not exist
	@Test
	public void testDoesNotExist() {
		ReservationDB db = new ReservationDB();
		db.Setup(connection);
		try {
			assertEquals(false, ReservationExists("asdhfaf"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("EXCEPTION BABY!");
		}
	}
	
	//Test 4 for a null Flight ID
	@Test
	public void testnullFlightID() {
		//Add a Customer
		CustomerDB dbCus = new CustomerDB ("ehan2", "pass", "edward", "han", "ehan@mail.com", 123, 0);
		dbCus.Setup(connection);
		//Add a Reservation
		ReservationDB db = new ReservationDB("edward", "han", 150f, 0, "chicken","asdf", "ehan2", null);
		db.Setup(connection);
		try {
			dbCus.Insert();
			db.Insert();
		} catch (SQLException e) {
			System.out.println("null flight ID Exception!");
			try {
				assertEquals(false, ReservationExists("ehan2"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 5 for a null Username
	@Test
	public void testnullUsername() {
		//Add a Flight
		FlightDB dbFlight =  new FlightDB("AC8082", "Air Canada", "Vancouver", "2012-11-15 10:00:00", 
				   "Edmonton", "2012-11-15 12:00:00", AirplaneStatus.onTime, 10f,
				   10, "1A", 200f
				  );
		dbFlight.Setup(connection);
		//Add a Reservation
		ReservationDB db = new ReservationDB("edward", "han", 150f, 0, "chicken","asdf", null, "AC8282");
		db.Setup(connection);
		try {
			dbFlight.Insert();
			db.Insert();
		} catch (SQLException e) {
			System.out.println("null Username Exception!");
			try {
				assertEquals(false, ReservationExists(null));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 6 for a negative payment
	@Test
	public void testNegativePayment() {
		//Add a Customer
		CustomerDB dbCus = new CustomerDB ("ehan3", "pass", "edward", "han", "ehan@mail.com", 123, 0);
		dbCus.Setup(connection);
		//Add a Flight
		FlightDB dbFlight =  new FlightDB("AC8083", "Air Canada", "Vancouver", "2012-11-15 10:00:00", 
				   "Edmonton", "2012-11-15 12:00:00", AirplaneStatus.onTime, 10f,
				   10, "1A", 200f
				  );
		dbFlight.Setup(connection);
		//Add a Reservation
		ReservationDB db = new ReservationDB("edward", "han", -150f, 0, "chicken","asdf", "ehan3", "AC8083");
		db.Setup(connection);
		try {
			dbCus.Insert();
			dbFlight.Insert();
			db.Insert();
			assertEquals(false, ReservationExists("ehan3"));
		} catch (SQLException e) {
			System.out.println("negative payment exception");
			try {
				assertEquals(false, ReservationExists("ehan3"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 7 for negative points
	@Test
	public void testNegativePoints() {
		//Add a Customer
		CustomerDB dbCus = new CustomerDB ("ehan4", "pass", "edward", "han", "ehan@mail.com", 123, 0);
		dbCus.Setup(connection);
		//Add a Flight
		FlightDB dbFlight =  new FlightDB("AC8084", "Air Canada", "Vancouver", "2012-11-15 10:00:00", 
				   "Edmonton", "2012-11-15 12:00:00", AirplaneStatus.onTime, 10f,
				   10, "1A", 200f
				  );
		dbFlight.Setup(connection);
		//Add a Reservation
		ReservationDB db = new ReservationDB("edward", "han", 150f, -230, "chicken","asdf", "ehan4", "AC8084");
		db.Setup(connection);
		try {
			dbCus.Insert();
			dbFlight.Insert();
			db.Insert();
			assertEquals(false, ReservationExists("ehan4"));
		} catch (SQLException e) {
			System.out.println("negative points exception");
			try {
				assertEquals(false, ReservationExists("ehan4"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 8 for null first Name;
	@Test
	public void testNullFirstName() {
		//Add a Customer
		CustomerDB dbCus = new CustomerDB ("ehan5", "pass", "edward", "han", "ehan@mail.com", 123, 0);
		dbCus.Setup(connection);
		//Add a Flight
		FlightDB dbFlight =  new FlightDB("AC8084", "Air Canada", "Vancouver", "2012-11-15 10:00:00", 
				   "Edmonton", "2012-11-15 12:00:00", AirplaneStatus.onTime, 10f,
				   10, "1A", 200f
				  );
		dbFlight.Setup(connection);
		//Add a Reservation
		ReservationDB db = new ReservationDB(null, "han", 150f, 0, "chicken","asdf", "ehan5", "AC8084");
		db.Setup(connection);
		try {
			dbCus.Insert();
			dbFlight.Insert();
			db.Insert();
			assertEquals(false, ReservationExists("ehan5"));
		} catch (SQLException e) {
			System.out.println("null first name exception");
			try {
				assertEquals(false, ReservationExists("ehan5"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 9 for null last Name
	@Test
	public void testNullLastName() {
		//Add a Customer
		CustomerDB dbCus = new CustomerDB ("ehan6", "pass", "edward", "han", "ehan@mail.com", 123, 0);
		dbCus.Setup(connection);
		//Add a Flight
		FlightDB dbFlight =  new FlightDB("AC8085", "Air Canada", "Vancouver", "2012-11-15 10:00:00", 
				   "Edmonton", "2012-11-15 12:00:00", AirplaneStatus.onTime, 10f,
				   10, "1A", 200f
				  );
		dbFlight.Setup(connection);
		//Add a Reservation
		ReservationDB db = new ReservationDB("hello", "bluebear", 150f, 0, "chicken","asdf", "ehan6", "AC8085");
		db.Setup(connection);
		try {
			dbCus.Insert();
			dbFlight.Insert();
			db.Insert();
			assertEquals(false, ReservationExists("ehan6"));
		} catch (SQLException e) {
			System.out.println("null last name exception");
			try {
				assertEquals(false, ReservationExists("ehan6"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 10 for null meal
	@Test
	public void testNullMeal() {
		//Add a Customer
		CustomerDB dbCus = new CustomerDB ("ehan7", "pass", "edward", "han", "ehan@mail.com", 123, 0);
		dbCus.Setup(connection);
		//Add a Flight
		FlightDB dbFlight =  new FlightDB("AC8086", "Air Canada", "Vancouver", "2012-11-15 10:00:00", 
				   "Edmonton", "2012-11-15 12:00:00", AirplaneStatus.onTime, 10f,
				   10, "1A", 200f
				  );
		dbFlight.Setup(connection);
		//Add a Reservation
		ReservationDB db = new ReservationDB("hello", "bluebear", 150f, 0, null,"asdf", "ehan7", "AC8086");
		db.Setup(connection);
		try {
			dbCus.Insert();
			dbFlight.Insert();
			db.Insert();
			assertEquals(false, ReservationExists("ehan7"));
		} catch (SQLException e) {
			System.out.println("null meal exception");
			try {
				assertEquals(false, ReservationExists("ehan7"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 11 for null seat
	@Test
	public void testNullSeat() {
		//Add a Customer
		CustomerDB dbCus = new CustomerDB ("ehan8", "pass", "edward", "han", "ehan@mail.com", 123, 0);
		dbCus.Setup(connection);
		//Add a Flight
		FlightDB dbFlight =  new FlightDB("AC8087", "Air Canada", "Vancouver", "2012-11-15 10:00:00", 
				   "Edmonton", "2012-11-15 12:00:00", AirplaneStatus.onTime, 10f,
				   10, "1A", 200f
				  );
		dbFlight.Setup(connection);
		//Add a Reservation
		ReservationDB db = new ReservationDB("hello", "bluebear", 150f, 0, "chicken", null, "ehan8", "AC8087");
		db.Setup(connection);
		try {
			dbCus.Insert();
			dbFlight.Insert();
			db.Insert();
			assertEquals(false, ReservationExists("ehan8"));
		} catch (SQLException e) {
			System.out.println("null seat exception");
			try {
				assertEquals(false, ReservationExists("ehan8"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 12 invalid length 1st Name
	@Test
	public void testInvalidLengthFirstName() {
		//Add a Customer
		CustomerDB dbCus = new CustomerDB ("ehan9", "pass", "edward", "han", "ehan@mail.com", 123, 0);
		dbCus.Setup(connection);
		//Add a Flight
		FlightDB dbFlight =  new FlightDB("AC8088", "Air Canada", "Vancouver", "2012-11-15 10:00:00", 
				   "Edmonton", "2012-11-15 12:00:00", AirplaneStatus.onTime, 10f,
				   10, "1A", 200f
				  );
		dbFlight.Setup(connection);
		//Add a Reservation
		ReservationDB db = new ReservationDB("helloIAMTHEBIGGESTBADASSINTHEWHOLEWORLDHAHAHAHAHA!!!!!!!DFDFDF", "bluebear", 150f, 0, "chicken","asdf", "ehan9", "AC8088");
		db.Setup(connection);
		try {
			dbCus.Insert();
			dbFlight.Insert();
			db.Insert();
			assertEquals(false, ReservationExists("ehan9"));
		} catch (SQLException e) {
			System.out.println("null 1st name exception");
			try {
				assertEquals(false, ReservationExists("ehan9"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 13 invalid length last Name
	@Test
	public void testInvalidLengthLastName() {
		//Add a Customer
		CustomerDB dbCus = new CustomerDB ("ehan10", "pass", "edward", "han", "ehan@mail.com", 123, 0);
		dbCus.Setup(connection);
		//Add a Flight
		FlightDB dbFlight =  new FlightDB("AC8089", "Air Canada", "Vancouver", "2012-11-15 10:00:00", 
				   "Edmonton", "2012-11-15 12:00:00", AirplaneStatus.onTime, 10f,
				   10, "1A", 200f
				  );
		dbFlight.Setup(connection);
		//Add a Reservation
		ReservationDB db = new ReservationDB("hello", "bluebearisabigbadassbearthatcanwhipyourass", 150f, 0, "chicken","asdf", "ehan10", "AC8089");
		db.Setup(connection);
		try {
			dbCus.Insert();
			dbFlight.Insert();
			db.Insert();
			assertEquals(false, ReservationExists("ehan10"));
		} catch (SQLException e) {
			System.out.println("null invalid length exception");
			try {
				assertEquals(false, ReservationExists("ehan10"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}

	//Test 14 invalid length meal
	@Test
	public void testInvalidLengthMeal() {
		//Add a Customer
		CustomerDB dbCus = new CustomerDB ("ehan11", "pass", "edward", "han", "ehan@mail.com", 123, 0);
		dbCus.Setup(connection);
		//Add a Flight
		FlightDB dbFlight =  new FlightDB("AC8090", "Air Canada", "Vancouver", "2012-11-15 10:00:00", 
				   "Edmonton", "2012-11-15 12:00:00", AirplaneStatus.onTime, 10f,
				   10, "1A", 200f
				  );
		dbFlight.Setup(connection);
		//Add a Reservation
		ReservationDB db = new ReservationDB("hello", "bluebear", 150f, 0, "chickenistheshitmanILOVETHECHICKENVERYVERYVERYMUCH!!!","asdf", "ehan11", "AC8090");
		db.Setup(connection);
		try {
			dbCus.Insert();
			dbFlight.Insert();
			db.Insert();
			assertEquals(false, ReservationExists("ehan11"));
		} catch (SQLException e) {
			System.out.println("null invalid length exception");
			try {
				assertEquals(false, ReservationExists("ehan11"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}

	//Test 15 invalid length Seat

	@Test
	public void testInvalidLengthSeat() {
		//Add a Customer
		CustomerDB dbCus = new CustomerDB ("ehan12", "pass", "edward", "han", "ehan@mail.com", 123, 0);
		dbCus.Setup(connection);
		//Add a Flight
		FlightDB dbFlight =  new FlightDB("AC8091", "Air Canada", "Vancouver", "2012-11-15 10:00:00", 
				   "Edmonton", "2012-11-15 12:00:00", AirplaneStatus.onTime, 10f,
				   10, "1A", 200f
				  );
		dbFlight.Setup(connection);
		//Add a Reservation
		ReservationDB db = new ReservationDB("hello", "bluebear", 150f, 0, "chicken","asdfasdfasdfasdfasdfasdfasdfadfadfadfadfadsfadfadfdafadf", "ehan12", "AC8091");
		db.Setup(connection);
		try {
			dbCus.Insert();
			dbFlight.Insert();
			db.Insert();
			assertEquals(false, ReservationExists("ehan12"));
		} catch (SQLException e) {
			System.out.println("null invalid length exception");
			try {
				assertEquals(false, ReservationExists("ehan12"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 16 create null Flight and add null Flight ID to reserve
	@Test
	public void testAddnullUsername() {
		//Add a Customer
				CustomerDB dbCus = new CustomerDB (null, "pass", "edward", "han", "ehan@mail.com", 123, 0);
				dbCus.Setup(connection);
		//Add a Flight
		FlightDB dbFlight =  new FlightDB("AC8092", "Air Canada", "Vancouver", "2012-11-15 10:00:00", 
				   "Edmonton", "2012-11-15 12:00:00", AirplaneStatus.onTime, 10f,
				   10, "1A", 200f
				  );
		dbFlight.Setup(connection);
		//Add a Reservation
		ReservationDB db = new ReservationDB("edward", "han", 150f, 0, "chicken","asdf", null, "AC8292");
		db.Setup(connection);
		try {
			dbCus.Insert();
			dbFlight.Insert();
			db.Insert();
			assertEquals(false, ReservationExists("ehan13"));
		} catch (SQLException e) {
			System.out.println("null Username Exception!");
			try {
				assertEquals(false, ReservationExists("ehan13"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 17 create null Customer and add null username to a reservation
	@Test
	public void testAddnullFlightID() {
		//Add a Customer
				CustomerDB dbCus = new CustomerDB ("buddy", "pass", "edward", "han", "ehan@mail.com", 123, 0);
				dbCus.Setup(connection);
		//Add a Flight
		FlightDB dbFlight =  new FlightDB(null, "Air Canada", "Vancouver", "2012-11-15 10:00:00", 
				   "Edmonton", "2012-11-15 12:00:00", AirplaneStatus.onTime, 10f,
				   10, "1A", 200f
				  );
		dbFlight.Setup(connection);
		//Add a Reservation
		ReservationDB db = new ReservationDB("edward", "han", 150f, 0, "chicken","asdf", "buddy", null);
		db.Setup(connection);
		try {
			dbCus.Insert();
			dbFlight.Insert();
			db.Insert();
			assertEquals(false, ReservationExists("ehan14"));
		} catch (SQLException e) {
			System.out.println("null Username Exception!");
			try {
				assertEquals(false, ReservationExists("ehan14"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}

}
